I got a message from one of my friends in facebook that he was troubled by exporting data from an existing excel worksheet. I have post an article to introduce how exporting data from Excel worksheet with .NET. So I tell him the article in my blog. But he said he wanted to realize it through WPF. Suddenly, I realized that I should write some articles about WPF. So today, I want to share with you how to export data from Excel worksheet to datatable for WPF.
How to Export Data from Excel spreadsheetto Datatable for WPF
Before I realize this task, I have to install a professional excel component Spire.XLS for WPF to help me. Spire.XLS for WPF supports to operate Excel 97-2003, Excel 2007 and Excel 2010. However, .NET Framework and Visual Studio must be installed for using Spire.XLS for WPF.
Free Trial of Spire.XLS for WPF
How to Export Data from Excel spreadsheetto Datatable for WPF
Before I realize this task, I have to install a professional excel component Spire.XLS for WPF to help me. Spire.XLS for WPF supports to operate Excel 97-2003, Excel 2007 and Excel 2010. However, .NET Framework and Visual Studio must be installed for using Spire.XLS for WPF.
Free Trial of Spire.XLS for WPF
Step1. Create a new project.
- Create a new project by choosing WPF Application in Visual Studio.
- Set the Target framework property of this project in Solution Explorer to be .NET Framework 4.
- Add a button and dataGrid in MainWindow. The default button name is “Button1”. You can set Button1 Content property to be “Run” in its properties by right clicking it.
Step2. Add reference and project namespaces.
- Add Spire.XLS. Wpf.dll as reference in Project. The Default location of Spire.Doc for WPF is “C:\Program Files\e-iceblue\Spire.XLS for WPF”.
C#
namespace dataexport
{
/// <summary>
/// Interaction logic for MainWindow.xaml
/// </summary>
public partial class MainWindow : Window
{
public MainWindow()
{
InitializeComponent();
}
private void button1_Click(object sender, RoutedEventArgs e)
{
}
}
}
VB
Namespace dataexport
''' <summary>
''' Interaction logic for MainWindow.xaml
''' </summary>
Public Partial Class MainWindow
Inherits Window
Public Sub New()
InitializeComponent()
End Sub
Private Sub button1_Click(sender As Object, e As RoutedEventArgs)
End Sub
End Class
End Namespace
3. Add below namespaces at the top of the method.
C#
using Spire.Xls;
using System.Data;
VB
Imports Spire.Xls
Imports System.Data
Step3. Export data from excel worksheet to datatable
Workbook workbook = new Workbook();
workbook.LoadFromFile(@"D:\michelle\e-iceblue\Spire.XLS\Demos\Data\dataexport.xls", ExcelVersion.Version97to2003);
Worksheet sheet = workbook.Worksheets[0];
VB
Dim workbook As New Workbook()
workbook.LoadFromFile("D:\michelle\e-iceblue\Spire.XLS\Demos\Data\dataexport.xls", ExcelVersion.Version97to2003)
Dim sheet As Worksheet = workbook.Worksheets(0)
2. Export data from excel worksheet to datatable.
C#
DataTable dataTable = sheet.ExportDataTable();
DataView view = new DataView(dataTable);
this.dataGrid1.ItemsSource = view;
this.dataGrid1.AutoGenerateColumns = true;
VB
Dim dataTable As DataTable = sheet.ExportDataTable()
Dim view As New DataView(dataTable)
Me.dataGrid1.ItemsSource = view
Me.dataGrid1.AutoGenerateColumns = True
3. Press F5 and click “Run” in MainWindow, you can see the datatable as below picture.
C#
using Spire.Xls;
using System.Data;
VB
Imports Spire.Xls
Imports System.Data
Step3. Export data from excel worksheet to datatable
- Create a new Excel workbook and load an excel file from system.
Workbook workbook = new Workbook();
workbook.LoadFromFile(@"D:\michelle\e-iceblue\Spire.XLS\Demos\Data\dataexport.xls", ExcelVersion.Version97to2003);
Worksheet sheet = workbook.Worksheets[0];
VB
Dim workbook As New Workbook()
workbook.LoadFromFile("D:\michelle\e-iceblue\Spire.XLS\Demos\Data\dataexport.xls", ExcelVersion.Version97to2003)
Dim sheet As Worksheet = workbook.Worksheets(0)
2. Export data from excel worksheet to datatable.
C#
DataTable dataTable = sheet.ExportDataTable();
DataView view = new DataView(dataTable);
this.dataGrid1.ItemsSource = view;
this.dataGrid1.AutoGenerateColumns = true;
VB
Dim dataTable As DataTable = sheet.ExportDataTable()
Dim view As New DataView(dataTable)
Me.dataGrid1.ItemsSource = view
Me.dataGrid1.AutoGenerateColumns = True
3. Press F5 and click “Run” in MainWindow, you can see the datatable as below picture.
More About Spire.XLS for WPF
Spire.XLS for WPF is a professional and powerful Excel component which enables developers/programmers to operate Excel files with their WPF applications.
Spire.XLS for WPF is a professional and powerful Excel component which enables developers/programmers to operate Excel files with their WPF applications.